package com.example.victory.mysql;

import com.example.victory.entity.companyEntity.DeptAccountEntity;
import com.example.victory.entity.companyEntity.TxtjyrEntity;
import org.apache.ibatis.annotations.*;
import org.springframework.cache.annotation.CacheConfig;
import org.springframework.cache.annotation.Cacheable;

import java.util.List;
import java.util.Map;

@Mapper
@CacheConfig(cacheNames = {"inner-business-customer"})
public interface DeptAndAccountMysqlMapper {


    @Insert({"<script> INSERT dept (lx,nd,yyb, zc, jyl, " +
            "yj ) VALUES " +
            " <foreach collection='deptAccountEntities' item='item' index='index' separator=','> " +
            " (#{item.LX,jdbcType=VARCHAR},#{item.ND,jdbcType=VARCHAR},#{item.YYB,jdbcType=VARCHAR}," +
            " #{item.ZC,jdbcType=VARCHAR}," +
            " #{item.JYL,jdbcType=VARCHAR}, #{item.YJ,jdbcType=VARCHAR} ) </foreach>  </script> "})
    @Options(useGeneratedKeys = true, keyColumn = "id", keyProperty = "id")
    Long insertDeptBatch(@Param(value = "deptAccountEntities") List<DeptAccountEntity> deptAccountEntities);



    @Select({"SELECT a.RQ ND,IFNULL(b.ZC,0) ZC,IFNULL(b.JYL,0) JYL,IFNULL(b.YJ,0) YJ FROM " +
            " ( SELECT zrr AS RQ FROM txtjyr WHERE zrr = jyr AND zrr < DATE_FORMAT(now(), '%Y%m%d') ORDER BY jyr DESC LIMIT 15 ) a " +
            "  LEFT  JOIN ( SELECT nd AS RQ, ryid AS RYID,zc AS ZC,jyl AS JYL,yj AS YJ FROM account WHERE lx =3  AND ryid =#{accountId}  AND gxlx =#{relationType}  ) b ON a.RQ=b.RQ order by a.RQ  "})
    @Cacheable(key = "'staff:day:accountId:'+#p0+':relationType:'+#p1", unless = "#result==null")
    List<Map<String,String>> getStaffDay(@Param(value = "accountId") String accountId, @Param(value = "relationType") String relationType);

    @Select({"SELECT a.RQ ND,IFNULL(b.ZC,0) ZC,IFNULL(b.JYL,0) JYL,IFNULL(b.YJ,0) YJ FROM " +
            " ( select distinct(NY) as RQ  from txtjyr  where ny  BETWEEN   date_format(DATE_ADD(CURDATE(), INTERVAL -5 MONTH), '%Y%m')  AND DATE_FORMAT(now(), '%Y%m')  ) a " +
            "  LEFT  JOIN ( SELECT nd AS RQ, ryid AS RYID,zc AS ZC,jyl AS JYL,yj AS YJ FROM account WHERE lx =2  AND ryid =#{accountId}  AND gxlx =#{relationType}  ) b ON a.RQ=b.RQ order by a.RQ  "})
    @Cacheable(key = "'staff:month:accountId:'+#p0+':relationType:'+#p1", unless = "#result==null")
    List<Map<String,String>> getStaffMonth(@Param(value = "accountId") String accountId, @Param(value = "relationType") String relationType);


    @Select({"SELECT a.RQ ND,IFNULL(b.ZC,0) ZC,IFNULL(b.JYL,0) JYL,IFNULL(b.YJ,0) YJ FROM " +
            " ( select distinct(ND) as RQ  from txtjyr  where ny > date_format(DATE_ADD(CURDATE(), INTERVAL -5 YEAR), '%Y')  ) a " +
            "  LEFT  JOIN ( SELECT nd AS RQ, ryid AS RYID,zc AS ZC,jyl AS JYL,yj AS YJ FROM account WHERE lx =1  AND ryid =#{accountId}  AND gxlx =#{relationType}  ) b ON a.RQ=b.RQ order by a.RQ  "})
    @Cacheable(key = "'staff:year:accountId:'+#p0+':relationType:'+#p1", unless = "#result==null")
    List<Map<String,String>> getStaffYear(@Param(value = "accountId") String accountId, @Param(value = "relationType") String relationType);


    @Select({"SELECT a.RQ ND,IFNULL(b.ZC,0) ZC,IFNULL(b.JYL,0) JYL,IFNULL(b.YJ,0) YJ FROM " +
            " ( select distinct(ND) as RQ  from txtjyr  where ny > date_format(DATE_ADD(CURDATE(), INTERVAL -5 YEAR), '%Y')  ) a " +
            "  LEFT  JOIN ( SELECT  nd as RQ,zc as ZC,jyl as JYL,yj as YJ  FROM dept WHERE lx=1 and yyb=#{deptCode}  ) b ON a.RQ=b.RQ order by a.RQ  "})
    @Cacheable(key = "'dept:year:'+#p0+':lx:'+#p1", unless = "#result==null")
    List<Map<String,String>> getDeptYear(@Param(value = "deptCode") String deptCode);

    @Select({"SELECT a.RQ ND,IFNULL(b.ZC,0) ZC,IFNULL(b.JYL,0) JYL,IFNULL(b.YJ,0) YJ FROM " +
            " ( select distinct(NY) as RQ  from txtjyr  where ny  BETWEEN   date_format(DATE_ADD(CURDATE(), INTERVAL -5 MONTH), '%Y%m')  AND DATE_FORMAT(now(), '%Y%m')  ) a " +
            "  LEFT  JOIN ( SELECT  nd as RQ,zc as ZC,jyl as JYL,yj as YJ  FROM dept WHERE lx=2 and yyb=#{deptCode}  ) b ON a.RQ=b.RQ order by a.RQ  "})
    @Cacheable(key = "'dept:month:'+#p0+':lx:'+#p1", unless = "#result==null")
    List<Map<String,String>> getDeptMonth(@Param(value = "deptCode") String deptCode);

    @Select({"SELECT a.RQ ND,IFNULL(b.ZC,0) ZC,IFNULL(b.JYL,0) JYL,IFNULL(b.YJ,0) YJ FROM " +
            " ( SELECT zrr AS RQ FROM txtjyr WHERE zrr = jyr AND zrr < DATE_FORMAT(now(), '%Y%m%d') ORDER BY jyr DESC LIMIT 15  ) a " +
            "  LEFT  JOIN ( SELECT  nd as RQ,zc as ZC,jyl as JYL,yj as YJ  FROM dept WHERE lx=3 and yyb=#{deptCode}  ) b ON a.RQ=b.RQ order by a.RQ  "})
    @Cacheable(key = "'dept:day:'+#p0+':lx:'+#p1", unless = "#result==null")
    List<Map<String,String>> getDeptDay(@Param(value = "deptCode") String deptCode);


    @Insert({"<script> INSERT account (lx,nd,ryid, zc, jyl, " +
            "yj,gxlx ) VALUES " +
            " <foreach collection='accounts' item='item' index='index' separator=','> " +
            " (#{item.LX,jdbcType=VARCHAR},#{item.ND,jdbcType=VARCHAR},#{item.RYID,jdbcType=VARCHAR}," +
            " #{item.ZC,jdbcType=VARCHAR}," +
            " #{item.JYL,jdbcType=VARCHAR}, #{item.YJ,jdbcType=VARCHAR}, #{item.GXLX,jdbcType=VARCHAR}  ) </foreach>  </script> "})
    @Options(useGeneratedKeys = true, keyColumn = "id", keyProperty = "id")
    Long insertAccountBatch(@Param(value = "accounts") List<DeptAccountEntity> accounts);

    @Delete({"truncate table dept"})
    void truncateDept();

    @Delete({"truncate table account"})
    void truncateAccount();

    @Delete({"truncate table txtjyr"})
    void truncateTxtjyr();

    @Insert({"<script> INSERT txtjyr (zrr,jyr,nd, jd, yf, " +
            "xq,ny,nz,zh ) VALUES " +
            " <foreach collection='deptList' item='item' index='index' separator=','> " +
            " (#{item.zrr,jdbcType=VARCHAR},#{item.jyr,jdbcType=VARCHAR},#{item.nd,jdbcType=VARCHAR}," +
            " #{item.jd,jdbcType=VARCHAR}," +
            " #{item.yf,jdbcType=VARCHAR}, #{item.xq,jdbcType=VARCHAR}, #{item.ny,jdbcType=VARCHAR} , " +
            " #{item.nz,jdbcType=VARCHAR}, #{item.zh,jdbcType=VARCHAR}   ) </foreach>  </script> "})
    @Options(useGeneratedKeys = true, keyColumn = "id", keyProperty = "id")
    Long insertTxtjyrBatch(@Param(value = "deptList") List<TxtjyrEntity> deptList);


    @Select({" SELECT zrr FROM txtjyr WHERE zrr=#{dateTime}   "})
    String getNowTime(@Param(value = "dateTime") String dateTime);
}
